﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using WebBanThuoc.DTO;

namespace WebBanThuoc.DAO
{
    public class SanPham_DAO
    {
        private SqlConnection connection;
        //public bool hasData { set; get; }
        public SanPham_DAO(SqlConnection connection)
        {
            this.connection = connection;
        }

        private SanPham getBy(string whereCom, string orderCom)
        {
            SanPham data = null;
            string comStr = "Select * from [SanPham]";
            if (whereCom.Trim() != "")
                comStr += " where " + whereCom;
            if (orderCom.Trim() != "")
            {
                comStr += " order by " + orderCom;
            }

            try
            {
                IDataProvider dap = new DataProvider(connection);
                dap.ExecuteReader_Command(System.Data.CommandType.Text, comStr);
                //this.hasData = dap.HasData;
                if (dap.HasData)
                {
                    data = new SanPham();
                    dap.DaReader.Read();

                    data.MaSanPham = dap.DaReader["MaSanPham"].ToString();
                    data.TenSanPham = dap.DaReader["TenSanPham"].ToString();
                    data.ThanhPhan = dap.DaReader["ThanhPhan"].ToString();
                    data.CongDung = dap.DaReader["CongDung"].ToString();
                    data.CachDung = dap.DaReader["CachDung"].ToString();
                    data.MaLoaiSanPham = dap.DaReader["MaLoaiSanPham"].ToString();
                    data.MaNhaSanXuat = dap.DaReader["MaNhaSanXuat"].ToString();
                    data.NgayTao = Convert.ToDateTime(dap.DaReader["NgayTao"]);
                    data.NgaySua = Convert.ToDateTime(dap.DaReader["NgaySua"]);
                    data.GiaBan = Convert.ToDouble(dap.DaReader["GiaBan"]);
                    data.SoLuotXem = Convert.ToInt32(dap.DaReader["SoLuotXem"]);
                    data.KhuyenMai = dap.DaReader["KhuyenMai"].ToString();
                    data.GiaDaGiam = Convert.ToDouble(dap.DaReader["GiaDaGiam"]);
                    data.IsNew = Convert.ToBoolean(dap.DaReader["IsNew"]);
                    data.CoHang = Convert.ToBoolean(dap.DaReader["CoHang"]);
                    data.DanhGia = Convert.ToInt32(dap.DaReader["DanhGia"]);
                    data.MoTa = dap.DaReader["MoTa"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return data;
        }

        public SanPham getByKey(string MaSanPham)
        {
            return getBy(" [MaSanPham]='" + MaSanPham + "'", " [MaSanPham] asc");
        }

        private List<SanPham> getListBy(string whereCom, string orderCom)
        {
            List<SanPham> data = new List<SanPham>();
            string comStr = "Select * from [SanPham]";
            if (whereCom.Trim() != "")
                comStr += " where " + whereCom;
            if (orderCom.Trim() != "")
            {
                comStr += " order by " + orderCom;
            }

            try
            {
                IDataProvider dap = new DataProvider(connection);
                dap.ExecuteReader_Command(System.Data.CommandType.Text, comStr);
                //this.hasData = dap.HasData;
                if (dap.HasData)
                {
                    while (dap.DaReader.Read())
                    {
                        SanPham obj = new SanPham();

                        obj.MaSanPham = dap.DaReader["MaSanPham"].ToString();
                        obj.TenSanPham = dap.DaReader["TenSanPham"].ToString();
                        obj.ThanhPhan = dap.DaReader["ThanhPhan"].ToString();
                        obj.CongDung = dap.DaReader["CongDung"].ToString();
                        obj.CachDung = dap.DaReader["CachDung"].ToString();
                        obj.MaLoaiSanPham = dap.DaReader["MaLoaiSanPham"].ToString();
                        obj.MaNhaSanXuat = dap.DaReader["MaNhaSanXuat"].ToString();
                        obj.NgayTao = Convert.ToDateTime(dap.DaReader["NgayTao"]);
                        obj.NgaySua = Convert.ToDateTime(dap.DaReader["NgaySua"]);
                        obj.GiaBan = Convert.ToDouble(dap.DaReader["GiaBan"]);
                        obj.SoLuotXem = Convert.ToInt32(dap.DaReader["SoLuotXem"]);
                        obj.KhuyenMai = dap.DaReader["KhuyenMai"].ToString();
                        obj.GiaDaGiam = Convert.ToDouble(dap.DaReader["GiaDaGiam"]);
                        obj.IsNew = Convert.ToBoolean(dap.DaReader["IsNew"]);
                        obj.CoHang = Convert.ToBoolean(dap.DaReader["CoHang"]);
                        obj.DanhGia = Convert.ToInt32(dap.DaReader["DanhGia"]);
                        obj.MoTa = dap.DaReader["MoTa"].ToString();

                        data.Add(obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return data;
        }

        public List<SanPham> getListByKey()
        {
            return getListBy("", " [MaSanPham] asc");
        }

        public List<SanPham> getListByKey(string TenSanPham)
        {
            return getListBy(" [TenSanPham] like N'%" + TenSanPham + "%' ", " [MaSanPham] asc");
        }

        public List<SanPham> getListByKey_OrderMSP(string TenSanPham)
        {
            return getListBy(" [TenSanPham] like N'%" + TenSanPham + "%' ", " CONVERT(int, SUBSTRING([MaSanPham],3,Len([MaSanPham])-1)) asc");
        }

        private List<SanPham> getListBy(string whereCom, string orderCom, List<SqlParameter> listParameter)
        {
            List<SanPham> data = new List<SanPham>();
            string comStr = "Select * from [SanPham]";
            if (whereCom.Trim() != "")
                comStr += " where " + whereCom;
            if (orderCom.Trim() != "")
            {
                comStr += " order by " + orderCom;
            }

            try
            {
                IDataProvider dap = new DataProvider(connection);
                if (listParameter.Count > 0)
                {
                    dap.AddParameters(listParameter);
                }
                dap.ExecuteReader_Command(System.Data.CommandType.Text, comStr);
                //this.hasData = dap.HasData;
                if (dap.HasData)
                {
                    while (dap.DaReader.Read())
                    {
                        SanPham obj = new SanPham();

                        obj.MaSanPham = dap.DaReader["MaSanPham"].ToString();
                        obj.TenSanPham = dap.DaReader["TenSanPham"].ToString();
                        obj.ThanhPhan = dap.DaReader["ThanhPhan"].ToString();
                        obj.CongDung = dap.DaReader["CongDung"].ToString();
                        obj.CachDung = dap.DaReader["CachDung"].ToString();
                        obj.MaLoaiSanPham = dap.DaReader["MaLoaiSanPham"].ToString();
                        obj.MaNhaSanXuat = dap.DaReader["MaNhaSanXuat"].ToString();
                        obj.NgayTao = Convert.ToDateTime(dap.DaReader["NgayTao"]);
                        obj.NgaySua = Convert.ToDateTime(dap.DaReader["NgaySua"]);
                        obj.GiaBan = Convert.ToDouble(dap.DaReader["GiaBan"]);
                        obj.SoLuotXem = Convert.ToInt32(dap.DaReader["SoLuotXem"]);
                        obj.KhuyenMai = dap.DaReader["KhuyenMai"].ToString();
                        obj.GiaDaGiam = Convert.ToDouble(dap.DaReader["GiaDaGiam"]);
                        obj.IsNew = Convert.ToBoolean(dap.DaReader["IsNew"]);
                        obj.CoHang = Convert.ToBoolean(dap.DaReader["CoHang"]);
                        obj.DanhGia = Convert.ToInt32(dap.DaReader["DanhGia"]);
                        obj.MoTa = dap.DaReader["MoTa"].ToString();

                        data.Add(obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return data;
        }

        public List<SanPham> getListByKey(string TenSanPham, bool IsNew, DateTime NgayTao)
        {
            List<SqlParameter> listParameter = new List<SqlParameter>();

            SqlParameter inParamater1 = new SqlParameter();
            inParamater1.ParameterName = "@IsNew";
            inParamater1.Value = IsNew;
            inParamater1.SqlDbType = System.Data.SqlDbType.Bit;            

            SqlParameter inParamater2 = new SqlParameter();
            inParamater2.ParameterName = "@NgayTao";
            inParamater2.Value = NgayTao;
            inParamater2.SqlDbType = System.Data.SqlDbType.SmallDateTime;

            listParameter.Add(inParamater1);
            listParameter.Add(inParamater2);

            return getListBy(" [TenSanPham] like N'%" + TenSanPham + "%' and [IsNew]=@IsNew and [NgayTao]>=@NgayTao", " [MaSanPham] asc", listParameter);

        }

        public string createMaSanPham()
        {
            string result = "";
            try
            {
                IDataProvider dap = new DataProvider(connection);
                dap.ExecuteReader_Command(System.Data.CommandType.Text, "SELECT [dbo].[fnc_CreateNew_MaSanPham] () as col");
                //this.hasData = dap.HasData;
                if (dap.HasData)
                {
                    dap.DaReader.Read();
                    result = dap.DaReader["col"].ToString().Trim();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public int add(SanPham obj)
        {
            string comQuery = "INSERT INTO [SanPham] ([MaSanPham],[TenSanPham],[ThanhPhan]"
                + ",[CongDung],[CachDung],[MaLoaiSanPham],[MaNhaSanXuat],[GiaBan]"
                + ",[KhuyenMai],[GiaDaGiam],[IsNew],[CoHang],[MoTa])"
                + " VALUES (@MaSanPham,@TenSanPham,@ThanhPhan,@CongDung,@CachDung,@MaLoaiSanPham"
                + ",@MaNhaSanXuat,@GiaBan,@KhuyenMai,@GiaDaGiam,@IsNew,@CoHang,@MoTa)";
            try
            {
                IDataProvider dap = new DataProvider(connection);
                if (obj.MaSanPham == null || obj.MaSanPham == "")
                {
                    obj.MaSanPham = createMaSanPham();
                }
                dap.AddParameters("@MaSanPham", obj.MaSanPham, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@TenSanPham", obj.TenSanPham, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@ThanhPhan", obj.ThanhPhan, System.Data.SqlDbType.NText);
                dap.AddParameters("@CongDung", obj.CongDung, System.Data.SqlDbType.NText);
                dap.AddParameters("@CachDung", obj.CachDung, System.Data.SqlDbType.NText);
                dap.AddParameters("@MaLoaiSanPham", obj.MaLoaiSanPham, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@MaNhaSanXuat", obj.MaNhaSanXuat, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@GiaBan", obj.GiaBan, System.Data.SqlDbType.Decimal);
                dap.AddParameters("@KhuyenMai", obj.KhuyenMai, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@GiaDaGiam", obj.GiaDaGiam, System.Data.SqlDbType.Decimal);
                dap.AddParameters("@IsNew", obj.IsNew, System.Data.SqlDbType.Bit);
                dap.AddParameters("@CoHang", obj.CoHang, System.Data.SqlDbType.Bit);
                dap.AddParameters("@MoTa", obj.MoTa, System.Data.SqlDbType.NVarChar);

                return dap.ExecuteNonQuery_Command(System.Data.CommandType.Text, comQuery);
            }
            catch (Exception ex)
            {
                throw ex;
            }


        }

        private int delete(string whereCom)
        {
            string comUpdate = "DELETE FROM [SanPham] ";
            if (whereCom.Trim() != "")
                comUpdate += " where " + whereCom;
            try
            {
                IDataProvider dap = new DataProvider(this.connection);
                return dap.ExecuteNonQuery_Command(System.Data.CommandType.Text, comUpdate);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int deleteByKey(string MaSanPham)
        {
            return delete(" [MaSanPham]='" + MaSanPham + "'");
        }

        private int update(SanPham obj, string whereCom)
        {
            string comQuery = "UPDATE [SanPham] SET [MaSanPham]=@MaSanPham, [TenSanPham]=@TenSanPham, [ThanhPhan]=@ThanhPhan"
                + ", [CongDung]=@CongDung, [CachDung]=@CachDung, [MaLoaiSanPham]=@MaLoaiSanPham, [MaNhaSanXuat]=@MaNhaSanXuat"
                + ", [GiaBan]=@GiaBan, [KhuyenMai]=@KhuyenMai, [GiaDaGiam]=@GiaDaGiam, [IsNew]=@IsNew"
                + ", [CoHang]=@CoHang, [NgaySua]=@NgaySua, [MoTa]=@MoTa";
            if (whereCom.Trim() != "")
                comQuery += " where " + whereCom;
            try
            {
                IDataProvider dap = new DataProvider(connection);
                dap.AddParameters("@MaSanPham", obj.MaSanPham, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@TenSanPham", obj.TenSanPham, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@ThanhPhan", obj.ThanhPhan, System.Data.SqlDbType.NText);
                dap.AddParameters("@CongDung", obj.CongDung, System.Data.SqlDbType.NText);
                dap.AddParameters("@CachDung", obj.CachDung, System.Data.SqlDbType.NText);
                dap.AddParameters("@MaLoaiSanPham", obj.MaLoaiSanPham, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@MaNhaSanXuat", obj.MaNhaSanXuat, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@GiaBan", obj.GiaBan, System.Data.SqlDbType.Decimal);
                dap.AddParameters("@KhuyenMai", obj.KhuyenMai, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@GiaDaGiam", obj.GiaDaGiam, System.Data.SqlDbType.Decimal);
                dap.AddParameters("@IsNew", obj.IsNew, System.Data.SqlDbType.Bit);
                dap.AddParameters("@CoHang", obj.CoHang, System.Data.SqlDbType.Bit);
                dap.AddParameters("@NgaySua", DateTime.Now, System.Data.SqlDbType.SmallDateTime);
                dap.AddParameters("@MoTa", obj.MoTa, System.Data.SqlDbType.NVarChar);

                return dap.ExecuteNonQuery_Command(System.Data.CommandType.Text, comQuery);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public int updateByKey(SanPham sanpham, string MaSanPham)
        {
            return update(sanpham, " [MaSanPham]='" + MaSanPham + "' "); 
        }

        //private List<SanPham> getList_Paging_By(string whereCom, string orderCom, int pageIndex, int pageSize)
        private List<SanPham> getList_Paging_By(string whereCom, int pageIndex, int pageSize)
        {
            int begin = (pageIndex - 1) * pageSize + 1;
            int end = pageIndex * pageSize;
            List<SanPham> data = new List<SanPham>();

            if (whereCom.Trim() != "")
            {
                whereCom = " where " + whereCom;
            }

            string comStr = "Select * from (select *, ROW_NUMBER() Over(order by [NgayTao] desc) as rowNumber from [SanPham] " + whereCom + ") sp ";
            comStr += " where [rowNumber] between " + begin.ToString() + " and " + end.ToString();
            
            //if (orderCom.Trim() != "")
            //{
            //    comStr += " order by " + orderCom;
            //}

            try
            {
                IDataProvider dap = new DataProvider(connection);
                dap.ExecuteReader_Command(System.Data.CommandType.Text, comStr);
                //this.hasData = dap.HasData;
                if (dap.HasData)
                {
                    while (dap.DaReader.Read())
                    {
                        SanPham obj = new SanPham();

                        obj.MaSanPham = dap.DaReader["MaSanPham"].ToString();
                        obj.TenSanPham = dap.DaReader["TenSanPham"].ToString();
                        obj.ThanhPhan = dap.DaReader["ThanhPhan"].ToString();
                        obj.CongDung = dap.DaReader["CongDung"].ToString();
                        obj.CachDung = dap.DaReader["CachDung"].ToString();
                        obj.MaLoaiSanPham = dap.DaReader["MaLoaiSanPham"].ToString();
                        obj.MaNhaSanXuat = dap.DaReader["MaNhaSanXuat"].ToString();
                        obj.NgayTao = Convert.ToDateTime(dap.DaReader["NgayTao"]);
                        obj.NgaySua = Convert.ToDateTime(dap.DaReader["NgaySua"]);
                        obj.GiaBan = Convert.ToDouble(dap.DaReader["GiaBan"]);
                        obj.SoLuotXem = Convert.ToInt32(dap.DaReader["SoLuotXem"]);
                        obj.KhuyenMai = dap.DaReader["KhuyenMai"].ToString();
                        obj.GiaDaGiam = Convert.ToDouble(dap.DaReader["GiaDaGiam"]);
                        obj.IsNew = Convert.ToBoolean(dap.DaReader["IsNew"]);
                        obj.CoHang = Convert.ToBoolean(dap.DaReader["CoHang"]);
                        obj.DanhGia = Convert.ToInt32(dap.DaReader["DanhGia"]);
                        obj.MoTa = dap.DaReader["MoTa"].ToString();

                        data.Add(obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return data;
        }

        public List<SanPham> getList_Paging_ByKey(string MaLoaiSanPham, int pageIndex, int pageSize)
        {
            return getList_Paging_By(" [MaLoaiSanPham]='" + MaLoaiSanPham + "' ", pageIndex, pageSize); 
        }
        public List<SanPham> getList_Paging_ByKey(string tenSanPham, int giaBan, string maLoaiSanPham, int trang, int soSPTrenTrang)
        {
            string whereSql = "";

            if (tenSanPham != "Tên sản phẩm")
                whereSql += " [TenSanPham] like N'%" + tenSanPham + "%' and ";
            if (giaBan > 0)
            {
                switch (giaBan)
                {
                    case 100000:
                        whereSql += " [GiaDaGiam]<" + giaBan + " and ";
                        break;
                    case 500000:
                        whereSql += " [GiaDaGiam]<=" + giaBan + " and [GiaDaGiam]>=100000 and ";
                        break;
                    case 1000000:
                        whereSql += " [GiaDaGiam]<=" + giaBan + " and [GiaDaGiam]>=500000 and ";
                        break;
                    case 1000001:
                        whereSql += " [GiaDaGiam]>=" + giaBan + " and ";
                        break;
                }
            }

            if (maLoaiSanPham != "0")
            {
                whereSql += " [MaLoaiSanPham] = '" + maLoaiSanPham + "' and ";
            }

            whereSql += "1=1";

            List<SanPham> listSP = getList_Paging_By(whereSql, trang, soSPTrenTrang);
            return listSP;
        }
        private int totalNumberOfPagesBy(string whereCom, int pageSize)
        {
            int result = 0;
            try
            {
                string comStr = "select count([MaSanPham]) as col from [SanPham] ";
                if (whereCom.Trim() != "")
                {
                    comStr += " where " + whereCom;
                }
                

                IDataProvider dap = new DataProvider(connection);
                dap.ExecuteReader_Command(System.Data.CommandType.Text, comStr);
                //this.hasData = dap.HasData;
                if (dap.HasData)
                {
                    dap.DaReader.Read();
                    result = Convert.ToInt32(dap.DaReader["col"]);
                    if (result % pageSize != 0)
                    {
                        result = result / pageSize + 1;
                    }
                    else
                    {
                        result = result / pageSize;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public int totalNumberOfPagesByKey(string MaLoaiSanPham, int pageSize)
        {
            return totalNumberOfPagesBy(" [MaLoaiSanPham]='" + MaLoaiSanPham + "' ", pageSize);
        }
        public int totalNumberOfPagesByKey(string tenSanPham, int giaBan, string maLoaiSanPham, int pageSize)
        {

            string whereSql = "";

            if (tenSanPham != "Tên sản phẩm")
                whereSql += " [TenSanPham] like N'%" + tenSanPham + "%' and ";
            if (giaBan > 0)
            {
                switch (giaBan)
                {
                    case 100000:
                        whereSql += " [GiaDaGiam]<" + giaBan + " and ";
                        break;
                    case 500000:
                        whereSql += " [GiaDaGiam]<=" + giaBan + " and [GiaDaGiam]>=100000 and ";
                        break;
                    case 1000000:
                        whereSql += " [GiaDaGiam]<=" + giaBan + " and [GiaDaGiam]>=500000 and ";
                        break;
                    case 1000001:
                        whereSql += " [GiaDaGiam]>=" + giaBan + " and ";
                        break;
                }
            }

            if (maLoaiSanPham != "0")
            {
                whereSql += " [MaLoaiSanPham] = '" + maLoaiSanPham + "' and ";
            }

            whereSql += "1=1";

            return totalNumberOfPagesBy(whereSql, pageSize);
        }

        public List<SanPham> getList_Paging_isNew(int pageIndex, int pageSize)
        {
            return getList_Paging_By(" [isNew]='" + true + "'", pageIndex, pageSize);
        }
        public int totalNumberOfPages_isNew(int pageSize)
        {
            return totalNumberOfPagesBy(" [isNew]='" + true + "'", pageSize);
        }


        // lay San Pham Khuyen Mai
        public List<SanPham> getList_Paging_KM(int pageIndex, int pageSize)
        {
            return getList_Paging_By(" [KhuyenMai]<>'' or [GiaDaGiam]<[GiaBan]", pageIndex, pageSize);
        }
        public int totalNumberOfPages_KM(int pageSize)
        {
            return totalNumberOfPagesBy(" [KhuyenMai]<>'' or [GiaDaGiam]<[GiaBan]", pageSize);
        }

        private List<SanPham> getList_Paging_By(string whereCom, int pageIndex, int pageSize, List<SqlParameter> dataPrama)
        {
            int begin = (pageIndex - 1) * pageSize + 1;
            int end = pageIndex * pageSize;
            List<SanPham> data = new List<SanPham>();

            if (whereCom.Trim() != "")
            {
                whereCom = " where " + whereCom;
            }

            string comStr = "Select * from (select *, ROW_NUMBER() Over(order by [NgayTao] desc) as rowNumber from [SanPham] " + whereCom + ") sp ";
            comStr += " where [rowNumber] between " + begin.ToString() + " and " + end.ToString();

            //if (orderCom.Trim() != "")
            //{
            //    comStr += " order by " + orderCom;
            //}

            try
            {
                IDataProvider dap = new DataProvider(connection);
                if (dataPrama != null)
                {
                    dap.AddParameters(dataPrama);
                }
                dap.ExecuteReader_Command(System.Data.CommandType.Text, comStr);
                //this.hasData = dap.HasData;
                if (dap.HasData)
                {
                    while (dap.DaReader.Read())
                    {
                        SanPham obj = new SanPham();

                        obj.MaSanPham = dap.DaReader["MaSanPham"].ToString();
                        obj.TenSanPham = dap.DaReader["TenSanPham"].ToString();
                        obj.ThanhPhan = dap.DaReader["ThanhPhan"].ToString();
                        obj.CongDung = dap.DaReader["CongDung"].ToString();
                        obj.CachDung = dap.DaReader["CachDung"].ToString();
                        obj.MaLoaiSanPham = dap.DaReader["MaLoaiSanPham"].ToString();
                        obj.MaNhaSanXuat = dap.DaReader["MaNhaSanXuat"].ToString();
                        obj.NgayTao = Convert.ToDateTime(dap.DaReader["NgayTao"]);
                        obj.NgaySua = Convert.ToDateTime(dap.DaReader["NgaySua"]);
                        obj.GiaBan = Convert.ToDouble(dap.DaReader["GiaBan"]);
                        obj.SoLuotXem = Convert.ToInt32(dap.DaReader["SoLuotXem"]);
                        obj.KhuyenMai = dap.DaReader["KhuyenMai"].ToString();
                        obj.GiaDaGiam = Convert.ToDouble(dap.DaReader["GiaDaGiam"]);
                        obj.IsNew = Convert.ToBoolean(dap.DaReader["IsNew"]);
                        obj.CoHang = Convert.ToBoolean(dap.DaReader["CoHang"]);
                        obj.DanhGia = Convert.ToInt32(dap.DaReader["DanhGia"]);
                        obj.MoTa = dap.DaReader["MoTa"].ToString();

                        data.Add(obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return data;
        }

        public List<SanPham> getList_Paging_ByParam(string tenSanPham, int giaBan, string maLoaiSanPham, int trang, int soSPTrenTrang)
        {
            string whereSql = "";
            List<SqlParameter> dataPrama = new List<SqlParameter>();
            

            if (tenSanPham != "Tên sản phẩm")
            {
                whereSql += " [TenSanPham] like @TenSanPham and ";
                dataPrama.Add(
                new SqlParameter("@TenSanPham", System.Data.SqlDbType.NVarChar)
                {
                    Value = "%" + tenSanPham + "%"
                });
            }
            if (giaBan > 0)
            {
                switch (giaBan)
                {
                    case 100000:
                        whereSql += " [GiaDaGiam]<" + giaBan + " and ";
                        break;
                    case 500000:
                        whereSql += " [GiaDaGiam]<=" + giaBan + " and [GiaDaGiam]>=100000 and ";
                        break;
                    case 1000000:
                        whereSql += " [GiaDaGiam]<=" + giaBan + " and [GiaDaGiam]>=500000 and ";
                        break;
                    case 1000001:
                        whereSql += " [GiaDaGiam]>=" + giaBan + " and ";
                        break;
                }
            }

            if (maLoaiSanPham != "0")
            {
                whereSql += " [MaLoaiSanPham] = '" + maLoaiSanPham + "' and ";
            }

            whereSql += "1=1";

            List<SanPham> listSP = getList_Paging_By(whereSql, trang, soSPTrenTrang, dataPrama);
            return listSP;
        }

        private int totalNumberOfPagesBy(string whereCom, int pageSize, List<SqlParameter> dataPrama)
        {
            int result = 0;
            try
            {
                string comStr = "select count([MaSanPham]) as col from [SanPham] ";
                if (whereCom.Trim() != "")
                {
                    comStr += " where " + whereCom;
                }


                IDataProvider dap = new DataProvider(connection);
                if (dataPrama != null)
                {
                    dap.AddParameters(dataPrama);
                }
                dap.ExecuteReader_Command(System.Data.CommandType.Text, comStr);
                //this.hasData = dap.HasData;
                if (dap.HasData)
                {
                    dap.DaReader.Read();
                    result = Convert.ToInt32(dap.DaReader["col"]);
                    if (result % pageSize != 0)
                    {
                        result = result / pageSize + 1;
                    }
                    else
                    {
                        result = result / pageSize;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public int totalNumberOfPagesByParam(string tenSanPham, int giaBan, string maLoaiSanPham, int pageSize)
        {

            string whereSql = "";
            List<SqlParameter> dataPrama = new List<SqlParameter>();

            if (tenSanPham != "Tên sản phẩm")
            {
                whereSql += " [TenSanPham] like @TenSanPham and ";
                dataPrama.Add(
                new SqlParameter("@TenSanPham", System.Data.SqlDbType.NVarChar)
                {
                    Value = "%" + tenSanPham + "%"
                });
            }
            if (giaBan > 0)
            {
                switch (giaBan)
                {
                    case 100000:
                        whereSql += " [GiaDaGiam]<" + giaBan + " and ";
                        break;
                    case 500000:
                        whereSql += " [GiaDaGiam]<=" + giaBan + " and [GiaDaGiam]>=100000 and ";
                        break;
                    case 1000000:
                        whereSql += " [GiaDaGiam]<=" + giaBan + " and [GiaDaGiam]>=500000 and ";
                        break;
                    case 1000001:
                        whereSql += " [GiaDaGiam]>=" + giaBan + " and ";
                        break;
                }
            }

            if (maLoaiSanPham != "0")
            {
                whereSql += " [MaLoaiSanPham] = '" + maLoaiSanPham + "' and ";
            }

            whereSql += "1=1";

            return totalNumberOfPagesBy(whereSql, pageSize, dataPrama);
        }
    }
}